﻿CREATE PROCEDURE [dbo].[ReportWorkLoggedByCategory]
	@DateTimeFrom DateTime,
	@DateTimeTo DateTime,
	@PersonId int = 0,
	@ProjectId int = 0
AS
BEGIN

	SET NOCOUNT ON;

	select		[Group] = CategoryGroup.Name, 
				[Category] = Category.Name,
				[Minutes Logged] = sum(Minutes)
	from		Category
		inner join CategoryGroup on
			CategoryGroup.CategoryGroupId = Category.CategoryGroupId
		left outer join WorkCategory on
			Category.CategoryId = WorkCategory.CategoryId
		inner join WorkLog on
			WorkCategory.WorkLogId = WorkLog.WorkLogId
		inner join Person on
			Person.PersonId = WorkLog.PersonId
		inner join Task on
			Task.TaskId = WorkLog.TaskId
		inner join Project on 
			Project.ProjectId = Task.ProjectId
	where		Workdate > @DateTimeFrom
	and			Workdate < @DateTimeTo
	and			(@PersonId = 0 or Person.PersonId = @PersonId)
	and			(@ProjectId = 0 or Project.ProjectId = @ProjectId)
	group by	CategoryGroup.Name, Category.Name

END